package com.fengwk.support.jdbc;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import com.fengwk.support.util.ArrayUtils;
import com.fengwk.support.util.CollectionUtils;
import com.fengwk.support.util.DateUtils;
import com.fengwk.support.util.LoggerUtils;
import com.fengwk.support.util.MapUtils;
import com.fengwk.support.util.ObjectUtils;
import com.fengwk.support.util.RegexUtils;
import com.fengwk.support.util.StringUtils;
import com.fengwk.support.util.TypeUtils;
import com.fengwk.support.util.LoggerUtils.Logger;

public class JdbcDatabase implements JdbcDatabaseApi<JdbcDatabase> {
	
	private static final Logger LOG = LoggerUtils.getLogger(JdbcDatabase.class);
	
	private Map<String, DataSource> dataSourceMap;// 数据源
	private DataSource dataSource;// 当前数据源
	
	private String url;// 注入url
    private String username;// 注入username
    private String password;// 注入password
    private String driverClassName; // 注入driverClassName
    private boolean showSql = false;
    private boolean executeTimer = false;
    private String showSqlPre;
    
    private String defaultDateFormat = "yyyy-MM-dd HH:mm:ss";// 日期格式化
    private final String escapeSeparator = "__zy__";// 特殊标记.的分隔符
    
    private final ThreadLocal<Connection> connectionThreadLocal = new ThreadLocal<Connection>();// 当前线程连接
    private final ThreadLocal<Boolean> transactionThreadLocal = new ThreadLocal<Boolean>();// 手动事务开启标识
    private final ThreadLocal<Long> executeTimerThreadLocal = new ThreadLocal<Long>();
    
    /**
     * 加载配置
     * @param url
     * @param username
     * @param password
     * @param driverClassName
     */
    public JdbcDatabase load(String url, String username, String password, String driverClassName) {
    	this.url = url;
    	this.username = username;
    	this.password = password;
    	this.driverClassName = driverClassName;
    	return this;
    }
    
  /**
   * 加载数据源,当前数据源为最后一次加载的数据源
   * @param dataSourceName
   * @param dataSource
   * @return
   */
    public JdbcDatabase load(DataSource dataSource) {
    	this.dataSource = dataSource;
    	return this;
    }
    
    /**
     * 开启事务
     */
    @Override
    public JdbcDatabase openTransaction() throws JdbcException {
    	transactionThreadLocal.set(Boolean.TRUE);
    	openTransactionBase();
    	return this;
    }
    
    /**
     * 提交事务
     */
    @Override
    public JdbcDatabase commitTransaction() throws JdbcException {
    	transactionThreadLocal.set(Boolean.FALSE);
    	commitTransactionBase();
    	return this;
    }
    
    /**
     * 回滚事务
     * @throws JdbcException
     */
    @Override
    public JdbcDatabase rollbackTransaction() throws JdbcException {
    	Connection connection = connectionThreadLocal.get();
    	if(connection == null) {
    		return this;
    	}
    	if(ObjectUtils.equals(transactionThreadLocal.get(), Boolean.TRUE)) {
    		try {
    			connection.rollback();
    			LOG.debug("rollback transaction");
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			endTransaction();
    		}
    	}
    	return this;
    }
    
    /**
     * 增删改
     * @param sql
     * @param params
     * @return 成功返回操作记录数量,失败返回-1
     */
    @Override
    public int update(String sql, Object...params) throws JdbcException {
    	Connection connection = openTransactionBase();
    	PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			setIndexObject(ps, params);
			showSql(sql, params);
			if(executeTimer) executeTimer();
	  		int res = ps.executeUpdate();
	  		if(executeTimer) executeTimer();
			return res;
		} catch (SQLException e) {
			handleException(e);
		} finally {
			commitTransactionBase();
			release(ps, null);
		}
		return -1;
    }
    
    /**
     * 查询对象并封装
     * @param sql
     * @param beanClass
     * @param params
     * @return 无结果返回 null
     */
    @Override
    public <B> List<B> query(String sql, Class<B> beanClass, Object...params) throws JdbcException {
    	List<Map<String, Object>> list = query(sql, params);
    	List<B> resList = buildBean(list, beanClass);
    	if(CollectionUtils.isBlank(resList)) {
    		return null;
    	}
    	return resList;
    }
    
    /**
     * 查询对象并封装
     * @param sql
     * @param beanClass
     * @param collection
     * @return 无结果返回 null
     */
    @Deprecated
    @Override
    public <B, E> List<B> queryWithCollection(String sql, Class<B> beanClass, Collection<E> collection) throws JdbcException {
    	List<Map<String, Object>> list = query(sql, collection);
    	List<B> resList = buildBean(list, beanClass);
    	if(resList.size() < 0) {
    		return null;
    	}
    	return resList;
    }
    
    /**
     * 查询单独对象并封装
     * @param sql
     * @param beanClass
     * @param params
     * @return 若查询到多条记录返回第一条,无结果返回 null
     */
    @Override
    public <B> B querySingle(String sql, Class<B> beanClass, Object...params) throws JdbcException {
    	List<Map<String, Object>> list = query(sql, params);
    	List<B> resList = buildBean(list, beanClass);
    	if(CollectionUtils.isBlank(resList)) {
    		return null;
    	}
    	if(resList.size() > 1) {
    		throw new JdbcException("查询结果有多条记录");
    	}
    	return resList.get(0);
    }
    
    /**
     * 查询单独对象并封装
     * @param sql
     * @param beanClass
     * @param collection
     * @return 无结果返回 null
     */
    @Deprecated
    @Override
    public <B, E> B querySingleWithCollection(String sql, Class<B> beanClass, Collection<E> collection) throws JdbcException {
    	List<Map<String, Object>> list = query(sql, collection);
    	List<B> resList = buildBean(list, beanClass);
    	if(resList.size() < 0) {
    		return null;
    	}
    	return resList.get(0);
    }
    
    /**
     * 查询
     * @param sql
     * @param params
     * @return 无结果返回 null
     */
    @Override
    public List<Map<String, Object>> query(String sql, Object...params) throws JdbcException {
    	//sql . 符号转换
    	if(StringUtils.isBlank(sql)) {
    		return null;
    	}
    	String _sql = replaceSqlSelect(sql);
        Connection connection = openTransactionBase();
        List<Map<String, Object>> list = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
		try {
			ps = connection.prepareStatement(_sql);
			setIndexObject(ps, params);
			showSql(sql, params);
			if(executeTimer) executeTimer();
	        resultSet = ps.executeQuery();
	        if(executeTimer) executeTimer();
	        ResultSetMetaData metaData = resultSet.getMetaData();
	        int colsLen = metaData.getColumnCount();
	        while (resultSet.next()) {
	            Map<String, Object> map = new LinkedHashMap<String, Object>();
	            for (int i = 0; i < colsLen; i++) {
	                String columnLabel = metaData.getColumnLabel(i + 1);
	                Object colsValue = resultSet.getObject(columnLabel);
	                if (colsValue == null) {
	                	colsValue = "";
	                }
	                map.put(columnLabel, colsValue);
	            }
	            if(list == null) {
	            	list = new ArrayList<Map<String, Object>>();
	            }
	            list.add(map);
	        }
	        return list;
		} catch (SQLException e) {
			handleException(e);
		} finally {
			commitTransactionBase();// 提交事务
			release(ps, resultSet);
		}
		return null;
    }
    
    /**
     * 查询
     * @param sql
     * @param collection
     * @return
     */
    @Deprecated
    @Override
	public <E> List<Map<String, Object>> queryWithCollection(String sql, Collection<E> collection) throws JdbcException {
    	Object[] params = null;
    	if(CollectionUtils.isNotBlank(collection)) {
    		params = new Object[collection.size()];
    		Iterator<E> it = collection.iterator();
    		int index = 0;
    		while(it.hasNext()) {
    			params[index] = it.next();
    			index ++;
    		}
    	}
    	return query(sql, params);
    }
	
    /**
     * 获取最后插入的id,在事务中生效
     * @return 查询失败返回-1
     */
    @Override
    public Long getLastInsertId() throws JdbcException {
    	Long lastInsertId = new Long(((BigInteger) (query("select last_insert_id() _lastInsertId").get(0).get("_lastInsertId"))).toString());
    	return lastInsertId == 0 ? -1 : lastInsertId;
    }
    
    private String replaceSqlSelect(String sql) {
    	if(StringUtils.isBlank(sql)) {
    		return sql;
    	}
    	sql = sql.trim().replaceAll("\\s+", " ");
    	String select = RegexUtils.regex(sql, "(?i)select\\s{1}(.+(\\.).+)+\\s{1}from");
    	if(StringUtils.isBlank(select)) {
    		return sql;
    	}
    	select = select.replaceAll("(?i)^select ", "").replaceAll("(?i) from$", "");
    	String[] selectParams = select.split(",");
    	StringBuffer sb = new StringBuffer();
    	sb.append("select ");
    	for(int i = 0; i < selectParams.length; i ++) {
    		String selectParam = selectParams[i];
    		selectParam = selectParam.trim().replaceAll("(?i)\\s{1}as\\s", " ");
    		if(RegexUtils.match(selectParam, "\\S+\\s{1}\\S+\\.\\S+")) {
    			int index = selectParam.lastIndexOf(".");
    			selectParam = StringUtils.substring(selectParam, 0, index) + escapeSeparator + StringUtils.substring(selectParam, index + 1);
    		}
    		sb.append(selectParam + ",");
    	}
    	select = sb.substring(0, sb.length() - 1);
    	select += " from";
    	return sql.replaceAll("(?i)select\\s{1}(.+(\\.).+)+\\s{1}from", select);
    }
    
	private void commitTransactionBase() {
    	if(transactionThreadLocal.get() != null && transactionThreadLocal.get()) {
    		return;
    	}
    	Connection connection = connectionThreadLocal.get();
    	if(connection == null) {
    		return;
    	}
    	try {
    		connection.commit();
    		LOG.debug("commit transaction");
		} catch (SQLException e) {
			handleException(e);
		} finally {
			endTransaction();
		}
    }
    
    private void showSql(String sql, Object...params) {
    	if(showSql) {
    		Pattern pattern = Pattern.compile("\\?");
    		Matcher matcher = pattern.matcher(sql);
    		StringBuffer sb = new StringBuffer();
    		int offset = 0;
    		int index = 0;
    		String paramStr = null;
    		while(matcher.find()) {
    			if(ArrayUtils.isNotBlank(params) && params.length > index) {
    				paramStr = params[index] + "";
    			}
    			paramStr = paramStr + "";
    			int end =  matcher.end();
    			sb.append(StringUtils.substring(sql, offset, end - 1) + "'" + paramStr + "'");
    			offset = end;
    			index ++;
    		}
    		sb.append(sql.substring(offset));
    		LOG.info((showSqlPre == null ? "" : showSqlPre) + sb.toString() + ";");
    	}
    }
    
    private void executeTimer() {
    	Long timer = executeTimerThreadLocal.get();
    	if(timer == null) {
    		executeTimerThreadLocal.set(System.currentTimeMillis());
    	} else {
    	    LOG.info("sql execute ==> " + (System.currentTimeMillis() - timer));
    		executeTimerThreadLocal.remove();
    	}
    }
    
    private Connection openTransactionBase() {
    	Connection connection = connectionThreadLocal.get();
    	if(connection != null) {
    		return connection;
    	}
    	connection = getConnection();
    	try {
			connection.setAutoCommit(Boolean.FALSE);
			LOG.debug("open transaction");
		} catch (SQLException e) {
			handleException(e);
		}
    	connectionThreadLocal.set(connection);
    	return connection;
    }
    
    private void endTransaction() {
    	Connection connection = connectionThreadLocal.get();
    	if(connection == null) {
    		return;
    	}
    	recycleConnection(connection);
    	connectionThreadLocal.remove();
    	transactionThreadLocal.remove();
    }
	
	// 构建 java bean 对象
	private <B> List<B> buildBean(List<Map<String, Object>> list, Class<B> beanClass) {
    	if(CollectionUtils.isBlank(list)) {
    		return null;
    	}
    	List<B> resList = new LinkedList<B>();
    	for(Map<String, Object> columnMap: list) {
    		B bean = null;
        	try {
    			bean = beanClass.newInstance();
    			if(bean == null || MapUtils.isBlank(columnMap)) {
        			continue;
        		}
        		Iterator<Entry<String, Object>> it = columnMap.entrySet().iterator();
        		while (it.hasNext()) {
        			Entry<String, Object> colEntry = it.next();
    				String colName = colEntry.getKey();
    				Object colValue = colEntry.getValue();
    				fillBean(bean, colName, colValue);
    			}
        		
    		} catch (InstantiationException | IllegalAccessException e) {
    			if(columnMap != null && columnMap.size() == 1) {
    				try {
    					bean = ObjectUtils.build(beanClass, columnMap.get(columnMap.keySet().iterator().next()).toString());
					} catch (Exception e2) {
						handleException(e);
					}
    			} else {
    				handleException(e);
    			}
    		}
        	resList.add(bean);
    	}
    	return resList;
    }
    
    // 第一层填充对象
    private void fillBean(Object bean, String colName, Object colValue) {
    	String[] colNames = colName.split(escapeSeparator);
    	PropertyDescriptor pd = null;
		try {
			pd = new PropertyDescriptor(colNames[0], bean.getClass());
		} catch (IntrospectionException e) {
			// ignore
			return;
		}
		Method readMethod =  pd.getReadMethod();
		Class<?> fileType = readMethod.getReturnType();
    	if(colNames.length == 1 && pd != null) {
    		try {
				fillBean(bean, fileType, colValue, pd);
			} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException | InstantiationException | NoSuchMethodException | SecurityException e) {
				handleException(e);
			}
		}else {
			// 是一个bean对象
			Object fieldObj = null;
			try {
				fieldObj = readMethod.invoke(bean);
			} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
				handleException(e);
			}
			if(fieldObj == null) {
				try {
					fieldObj = fileType.newInstance();
				} catch (InstantiationException | IllegalAccessException e) {
					handleException(e);
				}
			}
			fillBean(fieldObj, StringUtils.trimPrefix(colName, colNames[0] + escapeSeparator), colValue);
			try {
				pd.getWriteMethod().invoke(bean, fieldObj);
			} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
				handleException(e);
			}
		}
    }
    
    // 第二层填充对象
	private <T> void fillBean(Object bean, Class<T> fieldType, Object fieldValue, PropertyDescriptor pd) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException, NoSuchMethodException, SecurityException {
    	if(TypeUtils.isBasicType(fieldType)) {
    		pd.getWriteMethod().invoke(bean, ObjectUtils.build(fieldType, fieldValue.toString()));
			return;
		}else if (TypeUtils.isList(fieldType) && !TypeUtils.isList(fieldValue.getClass())) {
			@SuppressWarnings("unchecked")
			List<Object> list = (List<Object>) pd.getReadMethod().invoke(bean);
			if(CollectionUtils.isBlank(list)) {
				list = new ArrayList<>();
			}
			list.add(fieldValue);
			pd.getWriteMethod().invoke(bean, list);
			return;
		}else if (TypeUtils.isSet(fieldType) && !TypeUtils.isSet(fieldValue.getClass())) {
			@SuppressWarnings("unchecked")
			Set<Object> set = (Set<Object>) pd.getReadMethod().invoke(bean);
			if(CollectionUtils.isBlank(set)) {
				set = new HashSet<>();
			}
			set.add(fieldValue);
			pd.getWriteMethod().invoke(bean, set);
			return;
		}else if (TypeUtils.isType(fieldType, BigDecimal.class) && !TypeUtils.isType(fieldValue.getClass(), BigDecimal.class)) {
			pd.getWriteMethod().invoke(bean, BigDecimal.class.getConstructor(fieldValue.getClass()).newInstance(fieldValue));
			return;
		}else if (TypeUtils.isType(fieldType, Date.class) && !TypeUtils.isType(fieldValue.getClass(), Date.class)) {
			Class<?> valueClass = fieldValue.getClass();
			Date date = null;
			if(TypeUtils.isType(valueClass, String.class)) {
				date = DateUtils.toDate((String) fieldValue, defaultDateFormat);
			}else if (TypeUtils.isType(valueClass, Date.class)) {
				date = (Date) fieldValue;
			}else if(TypeUtils.isType(valueClass, java.sql.Date.class)) {
				date = new Date(((java.sql.Date) fieldValue).getTime());
			}
			pd.getWriteMethod().invoke(bean, date);
			return;
		}else {
			pd.getWriteMethod().invoke(bean, fieldValue);
		}
    }
    
    // 字段填充
    private void setIndexObject(PreparedStatement ps, Object...params) {
    	if (ArrayUtils.isNotBlank(params)) {  
        	for(int i = 0; i < params.length; i ++) {  
  				try {
					ps.setObject(i + 1, params[i]);
				} catch (SQLException e) {
					handleException(e);
				}
  			}  
        }
    }
    
    // 基础获取数据库连接  
    private Connection getConnection() {
    	if(this.dataSource != null) {
    		try {
				return this.dataSource.getConnection();
			} catch (SQLException e) {
				handleException(e);
			}
    	}
    	try {
			Class.forName(driverClassName);
			return DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException | SQLException e) {
			handleException(e);
		}
    	return null;
    }
    
    // 回收数据库连接
    private void recycleConnection(Connection connection) {
    	try {
    		if(connection != null) {
    			connection.close();
    		}
		} catch (SQLException e) {
			handleException(e);
		}
    }
    
    // 释放资源
    private void release(PreparedStatement ps, ResultSet resultSet) {  
        if (resultSet != null) {  
            try {  
                resultSet.close();  
            } catch (SQLException e) {  
                handleException(e);
            }  
        }  
        if (ps != null) {  
            try {  
                ps.close();  
            } catch (SQLException e) {  
            	handleException(e);
            }  
        }
    }
    
    // 异常处理
    private void handleException(Exception e) throws JdbcException {
        LOG.error(e.getMessage());
    	throw new JdbcException(JdbcException.JDBC_ERROR_CODE, e.getMessage());
    }

	public Map<String, DataSource> getDataSourceMap() {
		return dataSourceMap;
	}

	public void setDataSourceMap(Map<String, DataSource> dataSourceMap) {
		this.dataSourceMap = dataSourceMap;
	}

	public DataSource getCurrentDataSource() {
		return dataSource;
	}

	public void setCurrentDataSource(DataSource currentDataSource) {
		this.dataSource = currentDataSource;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getDriverClassName() {
		return driverClassName;
	}

	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}

	public String getDefaultDateFormat() {
		return defaultDateFormat;
	}

	public void setDefaultDateFormat(String defaultDateFormat) {
		this.defaultDateFormat = defaultDateFormat;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public boolean isShowSql() {
		return showSql;
	}

	public void setShowSql(boolean showSql) {
		this.showSql = showSql;
	}

	public boolean isExecuteTimer() {
		return executeTimer;
	}

	public void setExecuteTimer(boolean executeTimer) {
		this.executeTimer = executeTimer;
	}

	String getShowSqlPre() {
		return showSqlPre;
	}

	void setShowSqlPre(String showSqlPre) {
		this.showSqlPre = showSqlPre;
	}
	
}
